package com.hongdee.crm.activity.service.impl;

import com.hongdee.annotation.MethodComment;
import com.hongdee.common.dao.IHibernateDao;
import com.hongdee.common.dao.IJdbcDao;
import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.activity.dao.ITCrmCarActivityDao;
import com.hongdee.crm.activity.entity.TCrmCarActivity;
import com.hongdee.crm.activity.entity.TCrmCarActivityAllocation;
import com.hongdee.crm.activity.service.ITCrmCarActivityService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.*;


/**
 * 名  称:TCrmCarActivityServiceImpl
 * 类  型: class
 * 描  述:
 * 路  径:com.hongdee.crm.activity.service.impl;
 * 作  者: 术洪超
 * 日  期:2017-02-24 14:41
 */
@Service
public class TCrmCarActivityServiceImpl implements ITCrmCarActivityService {

    private String tabName = "T_CRM_CAR_ACTIVITY";

    @Autowired
    private ITCrmCarActivityDao itCrmCarActivityDao;

    @Autowired
    private IJdbcDao iJdbcDao;

    @Autowired
    private IHibernateDao iHibernateDao;


    /**
     * @param uid    登录用户 ID
     * @param start  起始记录
     * @param limit  页面大小
     * @param status 活动状态  1进行中  0已停止
     * @param time   选择时间
     * @return Map
     * @throws Exception
     * @desc 通过 JDBCDao分页查询
     * @author 术洪超
     */

    @Override
    public Map page(String id, String status, String time, String start, String limit, String uid,String deptId,
                    String companyId,String area,String brand) throws Exception {


        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String date = DateUtils.getTimeStr("yyyy-MM-dd 23:59:59");

        Date date1 = sdf.parse(date);
        long ts1 = date1.getTime();
        Long preTime = DateUtils.addDayLongTime(ts1, -1);
        Long lastTime = DateUtils.addDayLongTime(ts1, 0);
        //System.out.println(date1 + ": " + ts1);

        //开始时间小于等于当前时间并且结束时间大于当前时间的，自动更新状态为进行中
        String hql1 = "update TCrmCarActivity set status = '1' where startDate <=:preTime and stopDate >:lastTime ";
        Map params1 = new HashMap();
        params1.put("preTime", preTime);
        params1.put("lastTime", lastTime);
        this.itCrmCarActivityDao.update(hql1, params1);

        Date date2 = sdf.parse(date);
        long ts2 = date2.getTime();
        Long preTime2 = DateUtils.addDayLongTime(ts2, -1);

        //停止时间小于当前时间的，自动更新状态为已停止
        String hql2 = " update TCrmCarActivity set status = '0' where stopDate <=:preTime";
        Map params2 = new HashMap();
        params2.put("preTime", preTime2);
        this.itCrmCarActivityDao.update(hql2, params2);


        StringBuffer sql = new StringBuffer("select ac.*,bc.o_name from " + tabName + " ac ");
        List pageParams = new ArrayList<>();
        sql.append(",");
        sql.append(" (select * from t_bc_organ  where 1=1  ");
        Constant.companyAndAreaAndBrand(uid,companyId,area,brand,sql,pageParams,"");
        sql.append(" ) bc ");
        sql.append(" where bc.id=ac.company_id ");


        if (!StringUtils.isEmpty(id)) {
            sql.append( " and ac.id = ?");
            pageParams.add(id);
        }
        if (!StringUtils.isEmpty(status)) {
            sql.append( " and ac.status = ?");
            pageParams.add(status);
        }

        if (!time.equals("") && !time.equals("1970-01-01T00:00:00.000Z") && !StringUtils.isEmpty(time)) {

            sql.append( " and ? between from_unixtime(ac.start_date/1000,'%Y-%m-%d') and from_unixtime(ac.stop_date/1000,'%Y-%m-%d') ");

            pageParams.add(time);
        }
        //发起部门
        if(!StringUtils.isEmpty(deptId)){
            sql.append( " and ac.start_department = ?");
            pageParams.add(deptId);
        }

        String oId = TokenUtil.getCompanyId(uid);
        System.out.println(oId);
        sql.append( " order by ac.create_time desc");
//        pageParams.add(oId);

        return itCrmCarActivityDao.page(sql.toString(), pageParams.toArray(), start, limit);
    }


    /**
     * @param id 主键 ID
     * @return Map
     * @throws Exception
     * @desc 查询详情
     * @author 术洪超
     */
    @Override
    public Map info(String id) throws Exception {
        if (StringUtils.isEmpty(id)) {
            throw new Exception("请选择活动");
        }
        String sql = "select * from " + tabName + " where id = ? ";
        return this.itCrmCarActivityDao.info(sql, id);
    }

    /**
     * @param data 要保存的活动实体
     * @return
     * @throws Exception
     * @desc 保存添加或者修改的实体内容，通过主键判断  状态   0进行中  1已停止  2未开始
     * @author 术洪超
     */

    @Override
    public TCrmCarActivity saveOrUpdate(TCrmCarActivity data, String uid) throws Exception {


        if (data.getStartDate() > data.getStopDate()) {
            throw new Exception("结束时间不能小于开始时间，请重新输入!");
        }

        if (data.getStopDate() < new Date().getTime()) {
            throw new Exception("活动结束时间小于当前时间，请重新输入!");
        }

        List params = new ArrayList();
        String sql = "select name from t_crm_car_activity where company_id = ? and name = ?";
        params.add(TokenUtil.getCompanyId(uid));
        params.add(data.getName());

        if (data.getId() != null) {
            sql += " and id != ? ";
            params.add(data.getId());
        }

        List<Map<String, Object>> list = this.itCrmCarActivityDao.list(sql, params.toArray());
        if (null != list && list.size() > 0) {
            throw new Exception("活动名称已存在！");
        }
        if (null != data.getId()) {
            data.setUpdateUser(uid);
            data.setUpdateTime(new Date().getTime());
        } else {
            data.setCreateTime(new Date().getTime());
            data.setCreateDate(new Date().getTime());
            data.setCreateUser(uid);
        }

        if (data.getStartDate() > new Date().getTime()) {
            //活动开始时间小于当前时间   活动未开始
            data.setStatus("2");
        }

        if ((data.getStartDate() <= new Date().getTime()) && (data.getStopDate() >= new Date().getTime())) {
            //活动开始时间大于当前时间   活动进行中
            data.setStatus("1");
        }


        data.setType("4");
        data.setDepartment(TokenUtil.getDeptId(uid));

        this.itCrmCarActivityDao.saveOrUpdate(data);
        return data;
    }

    /**
     * @param id     主键 ID
     * @param status 状态
     * @return
     * @throws Exception
     * @desc 修改状态
     * @author 术洪超
     */
    @Override
    public void update(String id, String status) throws Exception {

        if (StringUtils.isEmpty(id)) {
            throw new Exception("请选择活动");
        } else {
            String hql = "update TCrmCarActivity set status =:status where id =:id ";
            Map params = new HashMap();
            params.put("id", id);
            params.put("status", status);
            this.itCrmCarActivityDao.update(hql, params);
        }
    }


    /**
     * @param id       主键 ID
     * @param stopDate 新的活动截止时间
     * @return
     * @throws Exception
     * @desc 延长活动时间
     * @author 术洪超
     */
    @Override
    public void exTime(String id, Long stopDate) throws Exception {

        if (stopDate == 0) {
            throw new Exception("请填写要修改的时间");
        } else {
            String hql = "update TCrmCarActivity set stopDate =:stopDate, status = '1' where id =:id";
            Map params = new HashMap();
            params.put("id", id);
            params.put("stopDate", stopDate);
            this.itCrmCarActivityDao.update(hql, params);
        }


    }

    /**
     * @param uid 当前登录用户
     * @return List<Map>
     * @throws Exception
     * @desc 活动的下拉
     * @author 术洪超
     */

    @Override
    public List<Map> combo(String uid, String inUse,String companyId) throws Exception {

        List params = new ArrayList();
        StringBuffer sql =new StringBuffer( " select * from " + tabName + " where 1 = 1 and company_id in( ");
        if("".equals(companyId)) {
            List<Map<String,Object>> companyMaps=TokenUtil.getDataCompany(uid);
            for(int i=0;i<companyMaps.size();i++){
                sql.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                params.add(id);
                if(i<(companyMaps.size()-1)){
                    sql.append(" , ");
                }
            }
        }else{
            sql.append("?");
            params.add(companyId);
        }
        sql.append(")");
        String nowTime = DateUtils.getTimeStr(new Date(), "yyyy-MM-dd");
        Long preTimeLong = DateUtils.parseTime(nowTime + " 00:00:00", "yyyy-MM-dd 00:00:00");
        /***进行中的活动*/
        if ("1".equals(inUse)) {
            sql .append( " and stop_date >= ? and start_date <= ?");
            params.add(preTimeLong);
            params.add(new Date().getTime());
        }
        sql .append( " order by create_time desc");
        List<Map<String, Object>> mapList = this.itCrmCarActivityDao.list(sql.toString(), params.toArray());
        List<Map> resList = new ArrayList<Map>();
        Map temp;
        for (Map map : mapList) {
            temp = new HashMap();
            temp.put("value", map.get("id"));
            temp.put("label", map.get("name"));
            resList.add(temp);
        }
        return resList;
    }

    /**
     * @param uid        登录用户 ID
     * @param start      起始记录
     * @param limit      页面大小
     * @param activityId 活动ID
     * @return Map
     * @throws Exception
     * @desc 分配活动之前查询未分配的车辆
     * @author 术洪超
     */

    @Override
    public Map alPage(String activityId, String start, String limit, String uid) throws Exception {

        String sql = "SELECT * FROM t_crm_car u WHERE 1 = 1 and "
                + "( u.id NOT IN ( SELECT a.USERCAR_ID FROM t_crm_car_activity_allocation a WHERE a.ACTIVITY_ID = ? )) "
                + " and u.company_id = ? order by a.create_time desc";

        List params = new ArrayList();
        params.add(activityId);
        params.add(TokenUtil.getCompanyId(uid));
        return itCrmCarActivityDao.page(sql, params.toArray(), start, limit);
    }

    /**
     * @param usercarId  要保存的车辆ID
     * @param uid        登录用户ID
     * @param activityId 要保存的活动ID
     * @return
     * @throws Exception
     * @desc 保存添加或者修改的实体内容，通过主键判断
     * @author 术洪超
     */
    @Override
    public void alSave(List<Map<Object, String>> usercarId, String uid, String activityId) throws Exception {

        for (Map carId : usercarId) {
            TCrmCarActivityAllocation activityAllocation = new TCrmCarActivityAllocation();
            activityAllocation.setActivityId(activityId);
            activityAllocation.setComboId(null);
            activityAllocation.setCompanyId(String.valueOf(TokenUtil.getCompanyId(uid)));
            activityAllocation.setUsercarId(String.valueOf(carId.get("id")));
            this.itCrmCarActivityDao.saveOrUpdate(activityAllocation);
        }

    }

    @Override
    public List<TCrmCarActivity> findList(String id, String status, String time, String start, String limit, String uid,String deptId,
                                          String companyId,String area,String brand) throws Exception {
//        List params = new ArrayList();
//        String sql = "select * from t_crm_car_activity where company_id = ?";
//        params.add(companyId);
        Map map1 = this.page( id,  status,  time,  "0",  "65536",  uid, deptId,
                 companyId, area, brand);
        List<Map<String, Object>> list1 =(List<Map<String,Object>>) map1.get("dataList");
//        List<Map<String, Object>> list1 = this.itCrmCarActivityDao.list(sql, params.toArray());
        List<TCrmCarActivity> crmCarActivityList = new ArrayList<>();
        for (Map map : list1) {
            TCrmCarActivity crmCarActivity = new TCrmCarActivity();

            crmCarActivity.setName(String.valueOf(map.get("name")));
            crmCarActivity.setGift(String.valueOf(map.get("gift")));
            crmCarActivity.setDegression(String.valueOf(map.get("degression")));
            crmCarActivity.setCost(String.valueOf(map.get("cost")));
            crmCarActivity.setGscost(String.valueOf(map.get("gscost")));
            crmCarActivity.setStartDate(Long.valueOf(String.valueOf(map.get("startDate"))));
            crmCarActivity.setStopDate(Long.valueOf(String.valueOf(map.get("stopDate"))));
            crmCarActivity.setQixian(String.valueOf(map.get("qixian")));
            crmCarActivity.setMessage(String.valueOf(map.get("message")));
            if (map.get("status").equals("2")) {
                crmCarActivity.setStatus("未开始");
            } else if (map.get("status").equals("1")) {
                crmCarActivity.setStatus("进行中");
            } else {
                crmCarActivity.setStatus("已停止");
            }


            crmCarActivityList.add(crmCarActivity);
        }
        return crmCarActivityList;
    }

    String[] excelHeader = {"活动名称", "赠送礼品", "礼品价值", "材料成本", "工时成本", "开始日期", "结束日期", "有效期限", "活动规则", "状态"};

    @Override
    public HSSFWorkbook export(List<TCrmCarActivity> list, String uid) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("活动管理");


        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeader.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeader[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽

            //设置指定列的列宽，256 * 30这种写法是因为width参数单位是单个字符的256分之一

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TCrmCarActivity tCrmCarActivity = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(tCrmCarActivity.getName());
            row.createCell(1).setCellValue(tCrmCarActivity.getGift());
            row.createCell(2).setCellValue(tCrmCarActivity.getCost());
            row.createCell(3).setCellValue(tCrmCarActivity.getDegression());
            row.createCell(4).setCellValue(tCrmCarActivity.getGscost());

            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Long startDate = tCrmCarActivity.getStartDate();
            String start = simpleDateFormat.format(startDate);
            row.createCell(5).setCellValue(start);

            Long stopDate = tCrmCarActivity.getStopDate();
            String stop = simpleDateFormat.format(stopDate);
            row.createCell(6).setCellValue(stop);

            row.createCell(7).setCellValue(tCrmCarActivity.getQixian() + "个月");
            row.createCell(8).setCellValue(tCrmCarActivity.getMessage());
            row.createCell(9).setCellValue(tCrmCarActivity.getStatus());

        }
        return wb;
    }

    public List activity(String uid) throws Exception {
        List params = new ArrayList();
        String sql = "SELECT * FROM(SELECT ta.id, CASE ta.status WHEN '2' THEN '未开始' WHEN '1' THEN '进行中' WHEN '0' THEN '已结束' END status, ta. name, (cast( ta.degression AS SIGNED INTEGER) + cast(ta.gscost AS SIGNED INTEGER)) * COUNT( IF (tc. status <> '-1', TRUE, NULL)) AS issueCost,(cast( ta.degression AS SIGNED INTEGER ) + cast(ta.gscost AS SIGNED INTEGER)) * COUNT( IF (tc. status = '2', TRUE, NULL)) AS usedCost,ta.gift, ta.start_date,ta.stop_date,CASE WHEN ta.stop_date < unix_timestamp() * 1000 THEN '100%' WHEN ta.start_date > unix_timestamp() * 1000 THEN '0%' ELSE concat( ROUND( " +
                "(unix_timestamp() * 1000 - ta.start_date) / (ta.stop_date - ta.start_date),2) * 100,'%')END ing,COUNT(IF (tc.status <> '-1', TRUE, NULL)) issue,COUNT( IF (tc. status = '2', TRUE, NULL)) used FROM t_crm_car_activity ta,t_crm_car_coupon tc WHERE ta.id = tc.activity_id AND ta.company_id = ? GROUP BY ta.id ORDER BY ta.start_date DESC) AS temp ";
        params.add(TokenUtil.getCompanyId(uid));
        List<Map<String, Object>> list1 = this.itCrmCarActivityDao.list(sql,params.toArray());
        List tCrmCarCouponList = new ArrayList<>();
        for (Map map : list1) {
            TCrmCarActivity tCrmCarActivity = new TCrmCarActivity();
            tCrmCarActivity.setName(String.valueOf(map.get("name")));


            tCrmCarActivity.setStatus(String.valueOf(map.get("status")));

            if (map.get("startDate") != null) {
                tCrmCarActivity.setStartDate(Long.valueOf(String.valueOf(map.get("startDate"))));
            } else {
                tCrmCarActivity.setStartDate(null);
            }
            if (map.get("stopDate") != null) {
                tCrmCarActivity.setStopDate(Long.valueOf(String.valueOf(map.get("stopDate"))));
            } else {
                tCrmCarActivity.setStopDate(null);
            }

            tCrmCarActivity.setCost(String.valueOf(map.get("cost")));
            tCrmCarActivity.setGscost(String.valueOf(map.get("gscost")));
            tCrmCarActivity.setGift(String.valueOf(map.get("issue")));
            tCrmCarActivity.setMessage(String.valueOf(map.get("issueCost")));
            tCrmCarActivity.setAddress(String.valueOf(map.get("used")));
            tCrmCarActivity.setQixian(String.valueOf(map.get("usedCost")));
            tCrmCarActivity.setDepartment(String.valueOf(map.get("ing")));

            tCrmCarCouponList.add(tCrmCarActivity);

        }
        return tCrmCarCouponList;
    }

    String[] excelHeaderRecordy = {"活动名称","状态","创建日期","截止日期","已进行","发券数","发券成本","已消费","消费成本","产值"};

    public HSSFWorkbook exportActivity(List<TCrmCarActivity> list, String uid) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("活动详情");


        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecordy.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordy[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TCrmCarActivity tCrmCarActivity = list.get(i);
            row = sheet.createRow(i + 1);

            row.createCell(0).setCellValue(tCrmCarActivity.getName());;
            row.createCell(1).setCellValue(tCrmCarActivity.getStatus());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Long startDate = tCrmCarActivity.getStartDate();
            String dDate = simpleDateFormat.format(startDate);
            row.createCell(2).setCellValue(dDate);
            Long stopDate=tCrmCarActivity.getStopDate();
            String sDate=simpleDateFormat.format(stopDate);
            row.createCell(3).setCellValue(sDate);
            row.createCell(4).setCellValue(tCrmCarActivity.getDepartment());
            row.createCell(5).setCellValue(tCrmCarActivity.getGift());
            row.createCell(6).setCellValue(tCrmCarActivity.getMessage());
            row.createCell(7).setCellValue(tCrmCarActivity.getAddress());
            row.createCell(8).setCellValue(tCrmCarActivity.getQixian());

        }
        return wb;
    }


    /**
     * 按条件查询活动导出
     */
    @Override
    public List ActivePageOut(String start, String limit,Map data, String uid,String name) throws Exception {
        String oid=TokenUtil.getCompanyId(uid);
        StringBuffer sqlBuffer = new StringBuffer();
        List params = new ArrayList();
        sqlBuffer.append("select * from (SELECT ");
        sqlBuffer.append("ta.id,");
        sqlBuffer.append("CASE ta.status when '2' then  '未开始' when '1' then '进行中' when '0' then '已结束' end status, ");
        sqlBuffer.append("ta.name,");
        //发券成本=（工时成本+物料成本）*优惠券状态不等于-1
        sqlBuffer.append("ROUND((cast(ta.degression as SIGNED INTEGER)+ cast(ta.gscost as SIGNED INTEGER))* COUNT(IF (tc. STATUS <> '-1'");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(end);
            }
        }
        sqlBuffer.append(", TRUE, NULL)),2)  AS issueCost,");
        //消费成本=（工时成本+物料成本）*优惠券状态等于2已使用
        sqlBuffer.append("ROUND((cast(ta.degression as SIGNED INTEGER)+ cast(ta.gscost as SIGNED INTEGER))* COUNT(IF (tc. STATUS =  '2' ");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.use_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.use_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end),+1));
            }
        }
        sqlBuffer.append(",TRUE,NULL)),2) AS usedCost,");
        sqlBuffer.append("ta.gift,ta.gscost,ta.cost,ta.degression, ");
        sqlBuffer.append("ta.start_date, ");
        sqlBuffer.append("ta.stop_date,tc.gongdan_id, ");
        sqlBuffer.append("CASE WHEN ta.stop_date<unix_timestamp()*1000 then '100%' WHEN ");
        sqlBuffer.append("ta.start_date>unix_timestamp()*1000 then '0%' ELSE ");
        sqlBuffer.append("concat(ROUND((unix_timestamp()*1000-ta.start_date)/(ta.stop_date-ta.start_date),2)*100,'%') END  ing,");
       /*原发券数issue改现 发放数issue=1已打印2已使用*/
        sqlBuffer.append("COUNT(if((tc.status='1' or tc.status='2')");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end),+1));
            }
        }
        sqlBuffer.append(",true,null)) issue,");
        //剩余数
        sqlBuffer.append("COUNT(if(tc.status='1' and tc.youxiaoqi>=?");
        params.add(DateUtils.getNowTime());
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end),+1));
            }
        }
        sqlBuffer.append(",true,null)) surplus,");
        //过期数量
        sqlBuffer.append("COUNT(if(tc.status='1' and tc.youxiaoqi<?");
        params.add(DateUtils.getNowTime());
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end),+1));
            }
        }
        sqlBuffer.append(",true,null)) expire,");
        //未打印
        sqlBuffer.append("COUNT(if(tc.status='0'");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end),+1));
            }
        }
        sqlBuffer.append(",true,null)) noprint,");
        //参与人数改名为创建数
        sqlBuffer.append("COUNT(if(tc.status<>'-1' ");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end),+1));
            }
        }
        sqlBuffer.append(",true,null)) joincount,");
        //原已消费改名为使用数
        sqlBuffer.append("COUNT(if( tc.status='2' ");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.use_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.use_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end),+1));
            }
        }
        sqlBuffer.append(",true,null)) used ,ta.start_department, ")
                .append(" SUM( if( tco.balance_date/1000 BETWEEN unix_timestamp(now())-604800 and unix_timestamp(now()) ,tco.real_amount,0)) 'match' ");
        sqlBuffer.append("FROM t_crm_car_activity ta ")
                .append(" left join t_crm_car_coupon tc on ta.id = tc.activity_id  ")
                .append(" left join t_crm_car_workorder tco on tco.order_num = tc.gongdan_id and tco.company_id = ?");
        params.add(oid);
        sqlBuffer.append("WHERE 1=1 ");
        if (!name.isEmpty()) {
            sqlBuffer.append(" and ta.name like ? ");
            params.add("%" + name + "%");
        }
        if (!oid.isEmpty()) {
            sqlBuffer.append(" and ta.company_id = ? ");
            params.add(oid);
        }
        //发起部门
        if (data.get("startDept") != "" && !StringUtils.isEmpty(data.get("startDept"))) {
            sqlBuffer.append(" and ta.start_department = ? ");
            params.add(data.get("startDept"));
        }
        sqlBuffer.append(" GROUP BY ta.id  ORDER BY ta.start_date desc) as temp ");
        List list = this.itCrmCarActivityDao.list(sqlBuffer.toString(), params.toArray());
        System.out.print(list);
        return list;
    }

    String[] excelHeaderRecordout = {"活动名称", "状态", "创建日期", "截止日期", "已进行", "发券数", "发券成本", "已消费", "消费成本", "产值"};

    public XSSFWorkbook ActivityExcel(List list, String uid) throws Exception {

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("活动详情");


        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }
        for (int i = 0; i < list.size(); i++) {

            Map map = (Map) list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(String.valueOf(map.get("name")));
            row.createCell(1).setCellValue(String.valueOf(map.get("status")));

            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            //Long startDate = tCrmCarActivity.getStartDate();
           // String start = simpleDateFormat.format(startDate);
            Long startDate=Long.valueOf(String.valueOf(map.get("startDate")));
            String ss=simpleDateFormat.format(startDate);
            row.createCell(2).setCellValue(ss);
            Long stopDate=Long.valueOf(String.valueOf(map.get("stopDate")));
            String dd=simpleDateFormat.format(stopDate);
            row.createCell(3).setCellValue(dd);
            row.createCell(4).setCellValue(String.valueOf(map.get("ing")));
            row.createCell(5).setCellValue(String.valueOf(map.get("issue")));
            row.createCell(6).setCellValue(String.valueOf(map.get("issueCost")));
            row.createCell(7).setCellValue(String.valueOf(map.get("used")));
            row.createCell(8).setCellValue(String.valueOf(map.get("usedCost")));

        }
        return wb;
    }
     /**
     * 获得正在进行中的活动信息
     * @return
     */
    @Override
    public List<Map<String,Object>> getActivityIng(String uid,String companyId,String vin) throws Exception {
        Long nowDate=System.currentTimeMillis();
        StringBuffer sql=new StringBuffer();
        if("".equals(companyId)){
            companyId=TokenUtil.getCompanyId(uid);
        }
        List<Object> params=new ArrayList<>();
        sql.append(" select * from  "+tabName+" where company_id=? ");
        params.add(companyId);
        if("".equals(vin)) {
            sql.append(" and start_date<=? ");
            params.add(nowDate);
            sql.append(" and stop_date>=? ");
            params.add(nowDate);
        }
        if(!"".equals(vin)){
            sql.append(" and id in ( select c.activity_id from t_crm_car_coupon c where c.vin=? " +
                    " and company_id=? and " +
                    " not EXISTS(select * from t_insurance_order_coupon where num=c.num) group by c.activity_id )");
            params.add(vin);
            params.add(companyId);
        }
        return this.itCrmCarActivityDao.list(sql.toString(),params.toArray());
    }

    @Override
    @MethodComment(name = "优惠券自动挂截工单")
    public void autoRefWorkOrder() throws Exception {
        //根据使用日志刷新工单 ID
        String sql = "UPDATE t_crm_car_coupon_userecord tr, " +
                " ( " +
                " SELECT " +
                "  r.id, " +
                "  max(o.id) AS gongdan_id " +
                " FROM " +
                "  t_crm_car_workorder o, " +
                "  t_crm_car_coupon_userecord r " +
                " WHERE " +
                "  o.company_id = r.company_id " +
                " AND o.plate_num = r.plate_num " +
                " AND r.gongdan_id IS NULL " +
                " AND FROM_UNIXTIME(r.use_date / 1000, '%Y%m%d') = FROM_UNIXTIME(o.balance_date / 1000, '%Y-%m-%d') " +
                " GROUP BY " +
                "  r.id " +
                ") temp " +
                "SET tr.gongdan_id = temp.gongdan_id " +
                "WHERE " +
                " tr.id = temp.id " +
                "AND " +
                " tr.gongdan_id IS NULL";
       int count =  this.itCrmCarActivityDao.executeSQL(sql);
        System.out.println("更新使用日志记录数："+count);
        // 刷新优惠券工单 ID
        String sql2 ="UPDATE t_crm_car_coupon c,  " +
                " t_crm_car_coupon_userecord r  " +
                "SET c.gongdan_id = r.gongdan_id  " +
                "WHERE  " +
                "  c.company_id = r.company_id  " +
                "AND c.activity_id = r.activity_id  " +
                "AND c.num = r.num  " +
                "AND c.gongdan_id IS NULL";

        count =  this.itCrmCarActivityDao.executeSQL(sql2);
        System.out.println("更新优慧券记录记录数："+count);
    }
}


